SQL Server must audit attempts to bypass access controls.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41021	SQL2-00-013400	SV-53396r1_rule		Medium

Description

Information system auditing capability is critical for accurate forensic analysis. Audit record content that may be necessary to satisfy the requirement of this control includes: time stamps, source and destination addresses, user/process identifiers, event descriptions, success/fail indications, file names involved, and access control or flow control rules invoked. Detection of suspicious activity, including access attempts and successful access from unexpected places, during unexpected times, or other unusual indicators, can support decisions to apply countermeasures to deter an attack. Without detection, malicious activity may proceed without hindrance. In SQL Server's case, this is a combination of the standard audit trace, as well as the operating system logs. Only the SQL Server logs are validated for this check, as the other part is dependent upon the operating system.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2014-01-17

Details

Check Text ( C-47638r2_chk )

From the query prompt: SELECT DISTINCT traceid FROM ::FN_TRACE_GETINFO('0') All currently defined traces for the SQL server instance will be listed. If no traces are returned, this is a finding. Determine the trace being used for the auditing requirement. Replace # with a traceid being used for the auditing requirements. From the query prompt: SELECT DISTINCT(eventid) FROM ::FN_TRACE_GETEVENTINFO('#') The required eventids 14, 15, 18, 20, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 115, 116, 117, 118, 128, 129, 130, 131, 132, 133, 134, 135, 152, 153, 170, 171, 172, 173, 175, 176, 177 and 178 should be listed. If any of the audit events or eventids required above are not listed, this is finding.

Fix Text (F-46320r2_fix)

Create and start an audit trace that audits required events. CREATE PROCEDURE fso_audit AS -- Create a Queue DECLARE @rc INT DECLARE @TraceID INT DECLARE @maxfilesize BIGINT DECLARE @fso_audit_log NVARCHAR(128) SET @maxfilesize = 5 -- Define custom @fso_audit_log to path\filename SET @fso_audit_log = 'd:\sqlserver\audit\fsoauditlog.log' EXEC @rc = SP_TRACE_CREATE @TraceID output, 6, @fso_audit_log, @maxfilesize, NULL IF (@rc != 0) GOTO Error -- Client side File and Table cannot be scripted. -- Set the events: DECLARE @on BIT SET @on = 1 -- Logins are audited based on SQL Server instance -- setting Audit Level stored in registry -- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.[#] \MSSQLServer\AuditLevel -- Audit Login -- Occurs when a user successfully logs in to SQL Server. EXEC SP_TRACE_SETEVENT @TraceID, 14, 1, @on -- TextData EXEC SP_TRACE_SETEVENT @TraceID, 14, 6, @on -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 14, 7, @on -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 14, 8, @on -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 14, 10, @on -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 14, 11, @on -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 14, 12, @on -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 14, 14, @on -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 14, 23, @on -- Success EXEC SP_TRACE_SETEVENT @TraceID, 14, 26, @on -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 14, 35, @on -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 14, 41, @on -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 14, 60, @on -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 14, 64, @on -- SessionLoginName -- Audit Logout -- Occurs when a user logs out of SQL Server. EXEC SP_TRACE_SETEVENT @TraceID, 15, 6, @on -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 15, 7, @on -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 15, 8, @on -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 15, 10, @on -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 15, 11, @on -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 15, 12, @on -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 15, 13, @on -- Duration EXEC SP_TRACE_SETEVENT @TraceID, 15, 14, @on -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 15, 15, @on -- EndTime EXEC SP_TRACE_SETEVENT @TraceID, 15, 23, @on -- Success EXEC SP_TRACE_SETEVENT @TraceID, 15, 26, @on -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 15, 35, @on -- DatabaseName EXEC SP_TRACE_SETEVENT @TraceID, 15, 41, @on -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 15, 60, @on -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 15, 64, @on -- SessionLoginName -- Audit Server Starts and Stops -- Occurs when the SQL Server service state is modified. EXEC SP_TRACE_SETEVENT @TraceID, 18, 6, @on -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 18, 7, @on -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 18, 8, @on -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 18, 10, @on -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 18, 11, @on -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 18, 12, @on -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 18, 14, @on -- StartTime EXEC SP_TRACE_SETEVENT @TraceID, 18, 23, @on -- Success EXEC SP_TRACE_SETEVENT @TraceID, 18, 26, @on -- ServerName EXEC SP_TRACE_SETEVENT @TraceID, 18, 41, @on -- LoginSid EXEC SP_TRACE_SETEVENT @TraceID, 18, 60, @on -- IsSystem EXEC SP_TRACE_SETEVENT @TraceID, 18, 64, @on -- SessionLoginName -- Audit Login Failed -- Indicates that a login attempt to SQL Server from a client failed. EXEC SP_TRACE_SETEVENT @TraceID, 20, 1, @on -- TextData EXEC SP_TRACE_SETEVENT @TraceID, 20, 6, @on -- NTUserName EXEC SP_TRACE_SETEVENT @TraceID, 20, 7, @on -- NTDomainName EXEC SP_TRACE_SETEVENT @TraceID, 20, 8, @on -- HostName EXEC SP_TRACE_SETEVENT @TraceID, 20, 10, @on -- ApplicationName EXEC SP_TRACE_SETEVENT @TraceID, 20, 11, @on -- LoginName EXEC SP_TRACE_SETEVENT @TraceID, 20, 12, @on -- SPID EXEC SP_TRACE_SETEVENT @TraceID, 20, 14, @on -- StartTime EXEC SP_TRACE_SET